#!/usr/bin/python3

Debug=False
import mysql.connector as MySQLdb #linux/Ubuntu
import time
from config import DB
from utils import timedatamake

class DataBase:
    api=[]
    def __init__(self):
        self.login=DB['user']
        self.passw=DB['passwd']
        self.db=DB['db']

    def apicall(self,method,*args,**kwargs):
        return self._execute(method,*args,**kwargs)

    def _execute(self,method,*args,**kwargs):
        response=None
        db=MySQLdb.connect(user=DB['user'],passwd=DB['passwd'],db=DB['db'])
        self.cursor=db.cursor()
        sql=getattr(self,method)(*args,**kwargs)
        if 'Set' in method:
            self.cursor.commit()
        response=self.cursor.fetchall()
        self.cursor.close()
        return response

    def GetBugsTopics(self,_id=None):
        print('started method')
        if not _id:
            self.cursor.execute("SELECT id,site,news from bishop_tbl where site='habrahabr' ORDER BY id DESC LIMIT 10;")








def Registr_login(email,password,login):
    """ function to registration login in db"""
    status=False
    db=MySQLdb.connect(user=DB['user'],passwd=DB['passwd'],db=DB['db'])
    cursor=db.cursor()
    try:
        date=timedatamake()
        cursor.execute("""INSERT INTO login_tbl VALUES(id,"%s","%s","%s","%s")""" % (login,date,password,email))
        db.commit()
        cursor.execute("""INSERT INTO Site_tbl (id) SELECT id from login_tbl where login="%s" and password="%s" """ % (login,password))
        db.commit()
        status=True
    except:
        pass
    finally:
        cursor.close()
        return status

def Cheklogin(login,password):
    """
    Check login
    """
    db=MySQLdb.connect(user=DB['user'],passwd=DB['passwd'],db=DB['db'])
    cursor=db.cursor()
    try:
        cursor.execute("""Select login,password from login_tbl where login="%s" and password="%s" """ % (login,password))
        response=cursor.fetchall()
        if response[0][0]==login:
            return True
        return False
    except Exception:
            return False

def ChekNewsForLogin(_id):
    """
    Check login for
    """
    db=MySQLdb.connect(user=DB['user'],passwd=DB['passwd'],db=DB['db'])
    cursor=db.cursor()
    try:
        cursor.execute("""Select * from Site_tbl where id="%s" """ % _id )
        response=cursor.fetchall()
        return response
    except Exception:
        return False
    finally:
        cursor.close()

def SetNewsForLogin(news,id_):
    db=MySQLdb.connect(user=DB['user'],passwd=DB['passwd'],db=DB['db'])
    cursor=db.cursor()
    try:
        cursor.execute("""UPDATE Site_tbl SET %s WHERE id="%s"; """ % (news,id_))
        db.commit()
        return True
    except Exception as err:
        return False
    finally:
        cursor.close()

def GetIdUser(user):
    db=MySQLdb.connect(user=DB['user'],passwd=DB['passwd'],db=DB['db'])
    cursor=db.cursor()
    try:
        cursor.execute("""Select id from login_tbl where login="%s" """ % user )
        response=cursor.fetchall()
        return response[0][0]
    except Exception:
        return False
    finally:
        cursor.close()

def GetContentPage(content_page):
    db=MySQLdb.connect(user=DB['user'],passwd=DB['passwd'],db=DB['db'])
    cursor=db.cursor()
    try:
        cursor.execute("""Select text from content_tbl where page="%s" """ % content_page)
        response=cursor.fetchall()[0][0]
        return response
    except:
        return False
    finally:
        cursor.close()

def SetContentPage(page,text):
    #For admin set text on page content#
    status=False
    db=MySQLdb.connect(user=DB['user'],passwd=DB['passwd'],db=DB['db'])
    cursor=db.cursor()
    cursor.execute("""Select text from content_tbl where page="%s" """ % page)
    response=cursor.fetchall()
    if not response:
        try:
            cursor.execute("""INSERT INTO content_tbl values("%s","%s")""" % (page,text))
            db.commit()
            status=True
            return status
        except Exception as err:
            if Debug:
                print(err)
        finally:
            cursor.close()
    try:
        cursor.execute("""UPDATE content_tbl SET text="%s" where page="%s" """ % (text,page))
        db.commit()
        status=True
        return status
    except Exception as err:
        if Debug:
            print(err)
    finally:
        cursor.close()

def NewsControlerforAdmin(command,msg=None,id=None):
    #admin news panel func#
    db=MySQLdb.connect(user=DB['user'],passwd=DB['passwd'],db=DB['db'])
    cursor=db.cursor()
    try:
        if command=='get':
            cursor.execute("""SELECT text from news_tbl WHERE id="%s" """ % id)
            response=cursor.fetchall()
            return response[0][0]
        if command=='add':
            date=time.ctime()
            cursor.execute("""INSERT INTO news_tbl VALUES(id,"%s","%s")""" % (date,msg))
            db.commit()
        if command=='change':
            cursor.execute("""UPDATE news_tbl SET text="%s" WHERE id="%s" """ % (msg,id))
            db.commit()
        if command=='delete':
            cursor.execute("""DELETE FROM news_tbl WHERE id="%s"; """ % (id))
            db.commit()
        if Debug:
            print("Command=%s,MSG=%s,ID=%s" % (command,msg,id) )
        cursor.close()
    except:
        cursor.close()
        return

def GetNewsOnsite():
    db=MySQLdb.connect(user=DB['user'],passwd=DB['passwd'],db=DB['db'])
    cursor=db.cursor()
    cursor.execute("SELECT * FROM news_tbl")
    response=cursor.fetchall()
    return response

#bugreport
def AddBugreport(user,text,name):
    status='open'
    date=timedatamake()
    db=MySQLdb.connect(user=DB['user'],passwd=DB['passwd'],db=DB['db'])
    cursor=db.cursor()
    cursor.execute("""INSERT INTO bugreport_tbl VALUES(id,"%s","%s","%s","%s","%s", "empty")""" % (user,date,text,name,status))
    db.commit()
    cursor.execute("""SELECT id from bugreport_tbl WHERE user="%s" and date="%s" """ %(user,date))
    response=cursor.fetchall()
    cursor.close()
    return response[0][0]

def GetBugsTopics(_id=None):
    if not _id:
        db=MySQLdb.connect(user=DB['user'],passwd=DB['passwd'],db=DB['db'])
        cursor=db.cursor()
        cursor.execute("SELECT id, name, date, status, user FROM bugreport_tbl")
        response=cursor.fetchall()
        cursor.close()
        return response

def GetTopicOfBugreport(_id):
    db=MySQLdb.connect(user=DB['user'],passwd=DB['passwd'],db=DB['db'])
    cursor=db.cursor()
    cursor.execute("""SELECT user,date,text,name,status, url_attach from bugreport_tbl where id="%s"  """ %(_id))
    topicdata=cursor.fetchall()
    cursor.execute("""SELECT user,date,text from comment_tbl where id="%s"  """ %(_id))
    comments=cursor.fetchall()
    cursor.close()
    return [topicdata[0],comments]

def AddComment(_id,user,text):
    date=timedatamake()
    db=MySQLdb.connect(user=DB['user'],passwd=DB['passwd'],db=DB['db'])
    cursor=db.cursor()
    cursor.execute("""INSERT INTO comment_tbl VALUES("%s","%s","%s","%s") """ %(_id,user,date,text))
    db.commit()
    cursor.close()
    return

def Updateurl(_id,url):
    db=MySQLdb.connect(user=DB['user'],passwd=DB['passwd'],db=DB['db'])
    cursor=db.cursor()
    cursor.execute("""UPDATE bugreport_tbl SET url_attach="%s" WHERE id="%s" """ % (url,_id))
    db.commit()
    cursor.close()
    return

def SearchTopic(sql):
    db=MySQLdb.connect(user=DB['user'],passwd=DB['passwd'],db=DB['db'])
    cursor=db.cursor()
    if sql=='':
        cursor.execute("""SELECT id, name, date, status, user FROM bugreport_tbl;""")
    else:
        cursor.execute("""SELECT id, name, date, status, user FROM bugreport_tbl where %s;""" %(sql))
    topic=cursor.fetchall()
    cursor.close()
    return topic

def ChangeTopikStatus(id,status):
    try:
        db=MySQLdb.connect(user=DB['user'],passwd=DB['passwd'],db=DB['db'])
        cursor=db.cursor()
        cursor.execute("""UPDATE bugreport_tbl SET status="%s" WHERE id="%s" """ % (status,id))
        db.commit()
    except:
        return False
    return True